﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class GpwfDAL:IGpwfDAL
    {
        public int Add(GpwfInfo wf)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":gnum", OracleType.VarChar, wf.Gnum),
                DALHelper.MakeInParam(":num", OracleType.VarChar, wf.Num),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, wf.Neirong),
                DALHelper.MakeInParam(":sl", OracleType.Number, wf.Sl),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, wf.Banzu),
                DALHelper.MakeInParam(":gday", OracleType.VarChar, wf.Gday)
            };
            return (DALHelper.RunCommand(@"insert into dd.gpwf values (:gnum, :num, :neirong, :sl, :banzu, to_date(:gday,'yyyy-mm-dd hh24:mi:ss'))", prams));
        }
        public int Update(string gnum, string neirong, decimal sl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":gnum", OracleType.VarChar, gnum),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong),
                DALHelper.MakeInParam(":sl", OracleType.Number, sl)
            };
            return (DALHelper.RunCommand(@"update dd.gpwf set sl = :sl, neirong = :neirong where gnum = :gnum", prams));
        }
        public int Del(string gnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":gnum", OracleType.VarChar, gnum)
            };
            return (DALHelper.RunCommand(@"delete from dd.gpwf where GNUM = :gnum", prams));
        }
        public DataSet Select(string gnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":gnum", OracleType.VarChar, gnum)
            };
            return (DALHelper.RunProcReturn(@"select jg.ku as ku, jg.guige as guige, wf.neirong as neirong, wf.sl as sl, wf.banzu as banzu, wf.gday as gday from dd.gpwf wf, dd.kc jg where wf.GNUM = :gnum and wf.num = jg.num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string sday, string banzu, string ku)
        {
            string bday = sday + "00:00:00";
            string eday = sday + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bday", OracleType.VarChar, bday),
                DALHelper.MakeInParam(":eday", OracleType.VarChar, eday),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, banzu),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };

            return (DALHelper.RunProcReturn(@"select wf.gnum as gnum, jg.ku as ku, jg.guige as guige, wf.gday as gday, wf.neirong as neirong, wf.sl as sl from dd.gpwf wf, dd.kc jg where wf.gday >= to_date(:bday,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:eday,'yyyy-mm-dd hh24:mi:ss') and wf.banzu = :banzu and wf.num = jg.num and jg.ku = :ku", prams, "Sbyday"));
        
        }
        
        public string AUTOID(string ku)
        {
            
            string Ntime = DateTime.Now.ToString();
            string dh = "";
            switch (ku)
            {
                case "嘉东库":
                    dh = "JD"; break;
                case "一高线":
                    dh = "YG"; break;
                case "二高线":
                    dh = "EG"; break;
                case "大棒线":
                    dh = "DB"; break;
                case "四轧库":
                    dh = "SZ"; break;
                case "板材库":
                    dh = "BC"; break;
                case "不锈钢热轧库":
                    dh = "BXR"; break;
                case "不锈钢冷轧库":
                    dh = "BXL"; break;
                case "碳钢热轧库":
                    dh = "TGR"; break;
                case "碳钢冷轧库":
                    dh = "TGL"; break;
                case "酸洗库":
                    dh = "SX"; break;
                default:
                    break;
            }

            return "GPWF" + dh + Ntime;
        }
        public string LZCWF(string cday, string guige)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":guige", OracleType.VarChar, guige)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and jg.ku not in ('嘉东库', '四轧库') and wf.num = jg.num and jg.guige = :guige and wf.neirong = '外发'", prams);
            return obj.ToString();
        
        }
        public string HFWF(string cday, string guige)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":guige", OracleType.VarChar, guige)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and jg.ku = '四轧库' and wf.num = jg.num and jg.guige = :guige wf.neirong = '外发'", prams);
            return obj.ToString();
        }
        public string CCPWF(string cday, string guige)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":guige", OracleType.VarChar, guige)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and jg.ku = '嘉东库' and wf.num = jg.num and jg.guige = :guige and wf.neirong = '外发'", prams);
            return obj.ToString();
        }
        public DataSet LZCGG()
        {
            return (DALHelper.RunProcReturn(@"select distinct guige from dd.kc where ku not in ('嘉东库', '四轧库') and pinzhong in ('方坯', '板坯')", "lzc"));
        }
        public DataSet HFGG()
        {
            return (DALHelper.RunProcReturn(@"select distinct guige from dd.kc where ku = '四轧库' and pinzhong = '方坯'", "hf"));
        }
        public DataSet CCPGG()
        {
            return (DALHelper.RunProcReturn(@"select distinct guige from dd.kc where ku = '嘉东库' and pinzhong in ('方坯', '板坯')", "lzc"));
        }
        public string NumByKG(string ku, string guige)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":guige", OracleType.VarChar, guige)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select num from dd.kc  where ku = :ku and guige = :guige", prams);
            return obj.ToString();
        }
        public string LZCFPCK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.ku = '四轧库' and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.neirong in ('外发', '倒库')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string BPDK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.pinzhong = '板坯' and jg.ku not in ('嘉东库') and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.neirong = '倒库'", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();

        }
        public string BPCK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.pinzhong = '板坯' and jg.ku = '嘉东库' and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.neirong in ('倒库', '外发')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string FPDK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.ku in ('一高线', '二高线', '大棒线') and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.neirong = '倒库'", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string FPCK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddDays(-1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.pinzhong = '方坯' and jg.ku = '嘉东库' and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.neirong in ('倒库', '外发')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string LZCFPKC(string cday)
        {
            string et = cday + "08:00:00";
            string bt = Convert.ToDateTime(cday).AddHours(-4).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jg.ku = '四轧库' and wf.neirong = '库存'", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string DKFPKC(string cday)
        {
            string et = cday + "08:00:00";
            string bt = Convert.ToDateTime(cday).AddHours(-4).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.pinzhong = '方坯' and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jg.ku = '嘉东库' and wf.neirong = '库存'", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string DKBPKC(string cday)
        {
            string et = cday + "08:00:00";
            string bt = Convert.ToDateTime(cday).AddHours(-4).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(wf.sl) from dd.gpwf wf, dd.kc jg where wf.num = jg.num and jg.pinzhong = '板坯' and wf.gday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and wf.gday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jg.ku = '嘉东库' and wf.neirong = '库存'", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
    }
}
